﻿using Dapper;
using Dapper.Contrib.Extensions;
using Gx.Common.Core;
using Gx.Dal.Extensions;
using Gx.Dal.Shared;
using Gx.Model.Entity;
using Gx.Model.Enums;
using Gx.Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
namespace Gx.Dal.Manage
{
    public class LKSettlementPaymentDal : BaseDal<LKSettlementPayment>
    {
        protected LKCustomerPrePaymentDal customerPrePayDal = new LKCustomerPrePaymentDal();
        /// <summary>
        /// 获取最大编号
        /// </summary>
        /// <returns></returns>
        public string GetMaxLKSettlementPaymentCode()
        {
            using (var conn = new DbContext())
            {
                return conn.Connection.QueryFirst<string>(@"SELECT MAX(CAST(SUBSTRING(BillNo, 2,LEN(BillNo)-1) AS int)) AS MaxBillNumber
FROM LK_SettlementPayment WITH(NOLOCK)
WHERE ISNUMERIC(SUBSTRING(BillNo, 2, LEN(BillNo) - 1)) = 1");
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public PagedList<LKSettlementPayment> GetLKSettlementPaymentPageList(LKSettlementPaymentInput input)
        {
            var ids = GetSettlementIdList(input);
            string sql = "SELECT * FROM LK_SettlementPayment WITH(NOLOCK) WHERE IsDeleted=0 @WHERE";
            List<string> conditions = new List<string>();
            //判断input的属性是否为空，如果不为空则添加到conditions中

            string condition = string.Empty;

            if (ids != null)
            {
                if (ids.Count == 0)
                {
                    ids.Add(0);
                }
                input.Ids = ids;
                conditions.Add("Id IN @Ids");
            }

            if (!string.IsNullOrWhiteSpace(input.BillNo))
            {
                input.BillNo = $"%{input.BillNo}%";
                conditions.Add("BillNo LIKE @BillNo");
            }
            if (input.CustomerId.HasValue)
            {
                conditions.Add("CustomerId=@CustomerId");
            }
            if (input.BeginTime.HasValue)
            {
                conditions.Add("PaymentTime>=@BeginTime");
            }
            if (input.EndTime.HasValue)
            {
                conditions.Add("PaymentTime<=@EndTime");
            }
            if (UserContext.RoleId != (int)Roles.管理员)
            {
                conditions.Add($"CompanyId={UserContext.CompanyId}");
            }
            if (conditions.Any())
            {
                condition = $" AND {string.Join(" AND ", conditions)}";
            }
            sql = !string.IsNullOrWhiteSpace(condition) ? sql.Replace("@WHERE", condition) : sql.Replace("@WHERE", "");

            using (var conn = new DbContext())
            {
                return conn.Connection.GetPagedList<LKSettlementPayment>(sql, "CreateTime Desc", input);
            }
        }

        /// <summary>
        /// 获取客户待收费明细
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public List<LKSettlementPaymentDetail> GetLKSettlementPaymentDetailListByCustomer(SelectCostInput input)
        {
            string lcwhere = string.Empty;
            string lccostwhere = string.Empty;
            string bcwhere = string.Empty;
            string bccostwhere = string.Empty;
            if (!string.IsNullOrWhiteSpace(input.GoodsNo))
            {
                lcwhere += " AND d.GoodsNo=@GoodsNo";
                lccostwhere += " AND d.GoodsNo=@GoodsNo";
                bcwhere += " AND c.ContractNo=@GoodsNo";
                bccostwhere += " AND d.GoodsNo=@GoodsNo";
            }
            if (input.BeginTime.HasValue) 
            {
                //lcwhere += " AND @BeginTime <=e.BeginTime";
                //lccostwhere += " AND @BeginTime <=cost.BeginTime";
                //bcwhere += " AND @BeginTime <=cd.BeginTime";
                //bccostwhere += " AND @BeginTime <=cd.BeginTime";

                lcwhere += " AND e.BeginTime >=@BeginTime";
                lccostwhere += " AND CONVERT(VARCHAR,cost.BeginTime, 23) >=@BeginTime";
                bcwhere += " AND cd.BeginTime >=@BeginTime";
                bccostwhere += " AND cd.BeginTime>=@BeginTime";
            }
            if (input.EndTime.HasValue)
            {
                //lcwhere += " AND @EndTime >=e.EndTime";
                //lccostwhere += " AND @EndTime >=cost.EndTime";
                //bcwhere += " AND @EndTime >=cd.EndTime";
                //bccostwhere += " AND @EndTime >=cd.EndTime";
                lcwhere += " AND e.BeginTime <=@EndTime";
                lccostwhere += " AND CONVERT(VARCHAR,cost.BeginTime, 23) <=@EndTime";
                bcwhere += " AND cd.BeginTime <=@EndTime";
                bccostwhere += " AND cd.BeginTime<=@EndTime";
            }
            using (var conn = new DbContext())
            {
                string sql = @"SELECT e.Id, e.BeginTime,e.EndTime,c.Days,d.GoodsNo ,d.GoodsId,d.GoodsName,d.GoodsSpecId,d.GoodsSpecName,
'冷藏'AS CostType,e.ContainerNumber,d.Weight,d.Number-COALESCE(od.OutNumber,0) AS Number,d.TonWeight,d.Area,d.PlateNumber,e.ColdUnitPrice,(d.TonWeight*e.ColdUnitPrice) AS ColdCost,c.ContractNo
FROM dbo.LK_WarehouseEntryDetail d WITH(NOLOCK)
JOIN dbo.LK_WarehouseEntry e WITH(NOLOCK) ON d.EntryId=e.Id
JOIN dbo.LK_ZeroWarehouseContract c WITH(NOLOCK) ON e.ContractNo=c.ContractNo
LEFT JOIN (SELECT GoodsNo,SUM(OutNumber) AS OutNumber,SUM(TonWeight) AS TonWeight FROM dbo.LK_WarehouseOutDetail WITH(NOLOCK)
WHERE IsDeleted=0
GROUP BY GoodsNo) AS  od  ON d.GoodsNo=od.GoodsNo
LEFT JOIN dbo.LK_SettlementPaymentDetail sd WITH(NOLOCK) ON d.GoodsNo=sd.GoodsNo AND sd.IsDeleted=0
WHERE d.IsDeleted=0 AND e.IsDeleted=0 AND c.IsDeleted=0 AND sd.GoodsNo IS NULL AND (od.GoodsNo IS NULL OR d.Number>od.OutNumber) AND e.CustomerId=@CustomerId {0}
UNION
SELECT e.Id, cost.BeginTime,cost.EndTime,c.Days,d.GoodsNo ,d.GoodsId,d.GoodsName,d.GoodsSpecId,d.GoodsSpecName,
'冷藏'AS CostType,e.ContainerNumber,d.Weight,d.Number-COALESCE(od.OutNumber,0) AS Number,d.TonWeight,d.Area,d.PlateNumber,e.ColdUnitPrice,(d.TonWeight*e.ColdUnitPrice) AS ColdCost,c.ContractNo
FROM dbo.LK_WarehouseEntryDetail d WITH(NOLOCK)
JOIN dbo.LK_WarehouseEntry e WITH(NOLOCK) ON d.EntryId=e.Id
JOIN dbo.LK_ZeroWarehouseCostDetail cost WITH(NOLOCK) ON e.BillNo=cost.BillNo
JOIN dbo.LK_ZeroWarehouseContract c WITH(NOLOCK) ON e.ContractNo=c.ContractNo
LEFT JOIN (SELECT GoodsNo,SUM(OutNumber) AS OutNumber,SUM(TonWeight) AS TonWeight FROM dbo.LK_WarehouseOutDetail WITH(NOLOCK)
WHERE IsDeleted=0
GROUP BY GoodsNo) AS  od  ON d.GoodsNo=od.GoodsNo
LEFT JOIN dbo.LK_SettlementPaymentDetail sd WITH(NOLOCK) ON d.GoodsNo=sd.GoodsNo AND sd.BeginTime=cost.BeginTime AND sd.EndTime=cost.EndTime AND sd.IsDeleted=0
WHERE d.IsDeleted=0 AND e.IsDeleted=0 AND c.IsDeleted=0 AND sd.GoodsNo IS NULL AND (od.GoodsNo IS NULL OR d.Number>od.OutNumber) AND e.CustomerId=@CustomerId {1}
UNION
SELECT DISTINCT 0, cd.BeginTime,cd.EndTime,0 AS Days,c.ContractNo AS GoodsNo ,0 AS GoodsId,'' AS GoodsName,0 AS GoodsSpecId,'' AS GoodsSpecName,
CASE c.StoreType 
WHEN 1 THEN '包面积'
WHEN 2 THEN '包卡位'
END
AS CostType,
'' AS ContainerNumber,0 AS Weight,1 AS Number,0 AS TonWeight,0 AS Area,0 AS PlateNumber,cd.TotalPrice AS ColdUnitPrice,cd.TotalPrice AS ColdCost,c.ContractNo
FROM dbo.LK_WarehouseContract c WITH(NOLOCK) 
JOIN dbo.LK_WarehouseContractDetail cd WITH(NOLOCK) ON c.id=cd.ContractId
LEFT JOIN dbo.LK_SettlementPaymentDetail sd WITH(NOLOCK) ON c.ContractNo=sd.GoodsNo AND sd.IsDeleted=0 AND sd.BeginTime=cd.BeginTime AND sd.EndTime=cd.EndTime
WHERE
--d.IsDeleted=0 AND e.IsDeleted=0 AND 
c.IsDeleted=0 AND cd.IsDeleted=0 
AND sd.GoodsNo IS NULL AND c.CustomerId=@CustomerId {2}
UNION
SELECT e.Id, e.EntryTime AS BeginTime,e.EntryTime AS EndTime,1 AS Days,d.GoodsNo ,d.GoodsId,d.GoodsName,d.GoodsSpecId,d.GoodsSpecName,
'冷藏'AS CostType,e.ContainerNumber,d.Weight,d.Number-COALESCE(od.OutNumber,0) AS Number,d.TonWeight,d.Area,d.PlateNumber,e.ColdUnitPrice,(d.TonWeight*e.ColdUnitPrice) AS ColdCost,c.ContractNo
FROM dbo.LK_WarehouseEntryDetail d WITH(NOLOCK)
JOIN dbo.LK_WarehouseEntry e WITH(NOLOCK) ON d.EntryId=e.Id
JOIN dbo.LK_WarehouseContract c WITH(NOLOCK) ON e.ContractNo=c.ContractNo
JOIN dbo.LK_WarehouseContractDetail cd WITH(NOLOCK) ON c.id=cd.ContractId
LEFT JOIN (SELECT GoodsNo,SUM(OutNumber) AS OutNumber,SUM(TonWeight) AS TonWeight FROM dbo.LK_WarehouseOutDetail WITH(NOLOCK)
WHERE IsDeleted=0
GROUP BY GoodsNo) AS  od  ON d.GoodsNo=od.GoodsNo
LEFT JOIN dbo.LK_SettlementPaymentDetail sd WITH(NOLOCK) ON d.GoodsNo=sd.GoodsNo AND sd.IsDeleted=0
WHERE d.IsDeleted=0 AND e.IsDeleted=0 AND c.IsDeleted=0 AND cd.IsDeleted=0 AND sd.GoodsNo IS NULL AND (od.GoodsNo IS NULL OR d.Number>od.OutNumber) 
-- AND cd.BeginTime<=GETDATE() AND cd.EndTime>=GETDATE() 
AND e.CustomerId=@CustomerId {3} ORDER BY ContractNo,BeginTime";

                sql = string.Format(sql, lcwhere, lccostwhere, bcwhere, bccostwhere);
                var result = conn.Connection.Query<LKSettlementPaymentDetail>(sql, input).ToList();

                var entryIds = result.Select(p => p.Id).Distinct().ToList();

                var costs = conn.Connection.Query<LKWarehouseEntryCost>("SELECT * FROM LK_WarehouseEntryCost WITH(NOLOCK) WHERE IsDeleted=0 AND EntryId IN @entryIds", new { entryIds });
                foreach (var item in result)
                {
                    item.Chuzhi = costs.Where(p => p.EntryId == item.Id && p.CostId == 2).Sum(p => p.UnitPrice * p.Number);
                    item.Zhuangxie = costs.Where(p => p.EntryId == item.Id && p.CostId == 3).Sum(p => p.UnitPrice * p.Number);
                    item.Fenjian = costs.Where(p => p.EntryId == item.Id && p.CostId == 4).Sum(p => p.UnitPrice * p.Number);
                    item.Guobang = costs.Where(p => p.EntryId == item.Id && p.CostId == 5).Sum(p => p.UnitPrice * p.Number);
                    item.Jiaban = costs.Where(p => p.EntryId == item.Id && p.CostId == 6).Sum(p => p.UnitPrice * p.Number);
                    item.Peisong = costs.Where(p => p.EntryId == item.Id && p.CostId == 7).Sum(p => p.UnitPrice * p.Number);
                    item.Qita = costs.Where(p => p.EntryId == item.Id && p.CostId == 8).Sum(p => p.UnitPrice * p.Number);
                }

                return result;
            }
        }


        public LKSettlementPaymentDetail GetLKSettlementPaymentDetailListByGoodsNo(string goodsNo, int customerId)
        {
            using (var conn = new DbContext())
            {
                var bccost = conn.Connection.QueryFirstOrDefault<LKSettlementPaymentDetail>(@"SELECT DISTINCT cd.BeginTime,cd.EndTime,0 AS Days,c.ContractNo AS GoodsNo ,0 AS GoodsId,'' AS GoodsName,0 AS GoodsSpecId,'' AS GoodsSpecName,
CASE c.StoreType 
WHEN 1 THEN '包面积'
WHEN 2 THEN '包卡位'
END
AS CostType,
'' AS ContainerNumber,0 AS Weight,1 AS Number,0 AS TonWeight,0 AS Area,0 AS PlateNumber,cd.TotalPrice AS ColdUnitPrice,cd.TotalPrice AS ColdCost
FROM dbo.LK_WarehouseEntryDetail d WITH(NOLOCK)
JOIN dbo.LK_WarehouseEntry e WITH(NOLOCK) ON d.EntryId=e.Id
JOIN dbo.LK_WarehouseContract c WITH(NOLOCK) ON e.ContractNo=c.ContractNo
JOIN dbo.LK_WarehouseContractDetail cd WITH(NOLOCK) ON c.id=cd.ContractId
LEFT JOIN (SELECT GoodsNo,SUM(OutNumber) AS OutNumber,SUM(TonWeight) AS TonWeight FROM dbo.LK_WarehouseOutDetail WITH(NOLOCK)
WHERE IsDeleted=0
GROUP BY GoodsNo) AS  od  ON d.GoodsNo=od.GoodsNo
LEFT JOIN dbo.LK_SettlementPaymentDetail sd WITH(NOLOCK) ON d.GoodsNo=sd.GoodsNo AND sd.IsDeleted=0
WHERE d.IsDeleted=0 AND e.IsDeleted=0 AND c.IsDeleted=0 AND cd.IsDeleted=0 AND sd.GoodsNo IS NULL AND (od.GoodsNo IS NULL OR d.Number>od.OutNumber) AND e.RentalWarehouseType=2
AND cd.BeginTime<=GETDATE() AND cd.EndTime>=GETDATE() AND e.CustomerId=@customerId AND d.GoodsNo =@goodsNo", new { customerId, goodsNo });


                return bccost;
            }
        }

        /// <summary>
        /// 新增收费单
        /// </summary>
        /// <param name="lKSettlementPaymentDto"></param>
        /// <returns></returns>
        public bool AddLKSettlementPayment(LKSettlementPaymentDto lKSettlementPaymentDto)
        {
            //获取客户余额
            var customerPrePay = customerPrePayDal.GetPreAmount(lKSettlementPaymentDto.SettlementPayment.CustomerId);
            return UseTransaction((conn, trans) =>
            {
                var settlement = lKSettlementPaymentDto.SettlementPayment;
                var result = conn.Insert(settlement, trans);
                if (result == 0) return false;

                var details = lKSettlementPaymentDto.Details;
                if (details != null && details.Any())
                {
                    details.ForEach(item => item.SettlementId = settlement.Id);
                    conn.Insert(details, trans);
                }

                //更新客户余额
                conn.Execute("UPDATE LK_Customer SET Balance=@Balance WHERE Id=@CustomerId", new { Balance = customerPrePay - settlement.PrePay, settlement.CustomerId }, trans);
                return result > 0;
            });
        }

        /// <summary>
        /// 编辑收费单
        /// </summary>
        /// <param name="lKSettlementPaymentDto"></param>
        /// <returns></returns>
        public bool EditLKSettlementPayment(LKSettlementPaymentDto lKSettlementPaymentDto)
        {
            //获取客户余额
            var customerPrePay = customerPrePayDal.GetPreAmount(lKSettlementPaymentDto.SettlementPayment.CustomerId);
            //获取当前收费单的预付扣款
            var sell = Get(lKSettlementPaymentDto.SettlementPayment.Id);
            decimal diff = 0;
            if (sell != null)
                diff = lKSettlementPaymentDto.SettlementPayment.PrePay - sell.PrePay;

            return UseTransaction((conn, trans) =>
            {
                var settement = lKSettlementPaymentDto.SettlementPayment;
                var result = conn.Update(settement, trans);

                var details = lKSettlementPaymentDto.Details;
                if (details != null && details.Any())
                {
                    var adds = details.Where(c => c.Id == 0).ToList();
                    var updates = details.Where(c => c.Id > 0).ToList();

                    if (updates.Any())
                    {
                        conn.Update(updates, trans);
                        conn.DeleteByWhere<LKSettlementPaymentDetail>("SettlementId=@SettlementId AND Id NOT IN @ids", new { SettlementId = settement.Id, ids = updates.Select(p => p.Id) }, trans);
                    }

                    if (adds.Any())
                    {
                        adds.ForEach(item => item.SettlementId = settement.Id);
                        conn.Insert(adds, trans);
                    }
                }
                else
                {
                    conn.DeleteByWhere<LKSettlementPaymentDetail>("SettlementId=@SettlementId", new { SettlementId = settement.Id }, trans);
                }

                //更新客户余额
                conn.Execute("UPDATE LK_Customer SET Balance=@Balance WHERE Id=@CustomerId", new { Balance = customerPrePay - diff, settement.CustomerId }, trans);

                return result;
            });
        }

        //删除收费单
        public bool DeleteLKSettlementPayment(int id)
        {
            return UseTransaction((conn, trans) =>
            {
                var result = conn.Execute("UPDATE LK_SettlementPayment SET IsDeleted=1 WHERE Id=@Id", new { Id = id }, trans);
                conn.Execute("UPDATE LK_SettlementPaymentDetail SET IsDeleted=1 WHERE SettlementId=@SettlementId", new { SettlementId = id }, trans);

                return result > 0;
            });
        }


        private List<int> GetSettlementIdList(LKSettlementPaymentInput input)
        {


            using (var conn = new DbContext())
            {
                string sql = @"SELECT d.SettlementId FROM dbo.LK_SettlementPaymentDetail d WITH(NOLOCK)
LEFT JOIN dbo.LK_Goods g WITH(NOLOCK) ON d.GoodsId=g.Id AND g.IsDeleted=0
LEFT JOIN dbo.LK_GoodsSpec gs WITH(NOLOCK) ON d.GoodsSpecId=gs.Id AND gs.IsDeleted=0
WHERE d.IsDeleted=0  @WHERE";

                List<string> conditions = new List<string>();

                if (!string.IsNullOrWhiteSpace(input.GoodsCode))
                {
                    input.GoodsCode = $"%{input.GoodsCode}%";
                    conditions.Add("d.GoodsNo LIKE @GoodsCode");
                }
                if (!string.IsNullOrWhiteSpace(input.GoodsName))
                {
                    input.GoodsName = $"%{input.GoodsName}%";
                    conditions.Add("g.GoodsName LIKE @GoodsName");
                }



                if (!conditions.Any()) return null;

                string condition = $" AND {string.Join(" AND ", conditions)}";

                sql = !string.IsNullOrWhiteSpace(condition) ? sql.Replace("@WHERE", condition) : sql.Replace("@WHERE", "");

                return conn.Connection.Query<int>(sql, input).ToList();
            }
        }
    }
}

